package com.css.zfzx.fxpg.modules.popgdp.repository;

import com.css.bpm.platform.utils.PlatformDateUtils;
import com.css.bpm.platform.utils.PlatformSessionUtils;
import com.css.bpm.platform.utils.UUIDGenerator;
import com.css.zfzx.fxpg.modules.popgdp.repository.entity.FxPopGDPEntity;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.PropertySource;
import org.springframework.stereotype.Repository;

import java.sql.*;
import java.util.List;

/**
 * 初始化数据
 * 解决上万条数据插入慢的问题
 *
 *
 */
@Repository
@PropertySource("application-dev.yml")
public class FxPopGDPDataImport {
    /**
     * 数据源
     * 10.19.41.111
     */
    @Value("${spring.datasource.url}")
    private String url = "";
    @Value("${spring.datasource.username}")
    private String name = "";
    @Value("${spring.datasource.password}")
    private String pwd = "";
    private static Connection conn;
    private static PreparedStatement ps;

    /**
     * 获取数据库连接
     * SQL语句
     */
    private Connection getConnection() throws Exception {
        Class.forName("com.mysql.cj.jdbc.Driver");
        conn = DriverManager.getConnection(url, name, pwd);
        return conn;
    }


    /**
     * sql 批量插入
     *
     * @return
     */
    public int sqlBatchSaveEntities(List<FxPopGDPEntity> list, String userId) throws Exception {
        int count = 0;
        /**
         * 获取数据库连接
         */
        conn = getConnection();

        StringBuffer sql = new StringBuffer("insert into fx_pop_gdp (id, province, city, area, affiliated_file_name, " +
                "coordinate, Shape_Leng, Shape_Area, ls_GDP, ls_Pop, " +
                "object_code, partition_flag, create_user, create_time, extend3 ) "
                + "values ");
        // 上万条数据
        for (FxPopGDPEntity entity : list) {
            count++;
            String id = UUIDGenerator.getUUID();
            String province = entity.getProvince();
            String city = entity.getCity();
            String area = entity.getArea();
            String affiliatedFileName = entity.getAffiliatedFileName();
            String coordinate = entity.getCoordinate();
            String shapeLeng = entity.getShape_Leng(); shapeLeng = (shapeLeng != null ? shapeLeng : "");
            String shapeArea = entity.getShape_Area(); shapeArea = (shapeArea != null ? shapeArea : "");
            String lsGDP = entity.getLs_GDP(); lsGDP = (lsGDP != null ? lsGDP : "");
            String lsPop = entity.getLs_Pop(); lsPop = (lsPop != null ? lsPop : "");
            String extend3 = entity.getExtend3();

            String objectCode = entity.getObjectCode();
            objectCode = (objectCode != null ? objectCode : "");
            String partitionFlag = entity.getPartitionFlag();
            partitionFlag = (partitionFlag != null ? partitionFlag : "");
            String createUser = PlatformSessionUtils.getUserId();
            createUser = (createUser != null ? createUser : userId);
            Timestamp createTime = PlatformDateUtils.getCurrentTimestamp();

            sql.append("('" + id + "','" + province + "','" + city + "','" + area + "','" + affiliatedFileName + "','" + coordinate +
                    "','" + shapeLeng + "','" + shapeArea + "','" + lsGDP + "','" + lsPop
                    + "','" + objectCode + "','" + partitionFlag + "','" + createUser + "','" + createTime + "','" + extend3 + "'),");
        }
        String sql_str = sql.toString();
        sql_str = sql_str.substring(0, sql_str.length() - 1);
        ps = conn.prepareStatement(sql_str);
        ps.executeUpdate(sql_str);
        if (ps != null) ps.close();
        if (conn != null) conn.close();
        return count;
    }

    public int sqlBatchDeleteEntities(List<FxPopGDPEntity> list) throws Exception {
        int count = 0;
        conn = getConnection();
        StringBuffer sql = new StringBuffer("DELETE FROM fx_pop_gdp WHERE id IN ( ");
        // 上万条数据
        for (FxPopGDPEntity entity : list) {
            count++;
            String id = entity.getId();
            sql.append("'" + id + "',");
        }
        String sql_str = sql.toString().substring(0, sql.length() - 1) + " )";
        ps = conn.prepareStatement(sql_str);
        ps.executeUpdate(sql_str);
        if (ps != null) ps.close();
        if (conn != null) conn.close();
        return count;
    }


}
